﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Itegracion.Framework.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data;
using System.Data.Common;

namespace Itegracion.iAlmacen.Model
{
    public class Persona
    {
        public static List<Element> TraerListado(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  e.id, c.nombre as tipo, e.nombre, e.direccion, e.docId, e.telefonos
                FROM
                  Entidad e
                  JOIN Constante c ON e.idTipo = c.oid and c.codigo='TipoEntidad'
                ORDER BY
                  C.nombre, e.id");
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> TraerCombo(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                  e.id,
                  e.nombre
                FROM
                  Entidad e
                WHERE
                  idTipo = @idTipo");
            db.AddInParameter(command, "@idTipo", DbType.Int32, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.Add(new Element("rpta", dt));

            return rpta;
        }
        public static List<Element> Traer(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                    e.id, e.nombre, e.direccion, e.docId, e.telefonos, e.idTipo
                FROM
                    Entidad e
                WHERE
                    e.id = @id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.AddRange(dt.Rows.ToList());

            return rpta;
        }
        public static List<Element> TraerByDocId(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                SELECT
                    e.nombre, e.direccion, e.docId, e.telefonos, e.idTipo
                FROM
                    Entidad e
                WHERE
                    e.docId = @docId");
            db.AddInParameter(command, "docId", DbType.String, entrada[0].Value);
            DataTable dt = db.ExecuteDataSet(command).Tables[0];
            rpta.AddRange(dt.Rows.ToList());

            return rpta;
        }
        public static List<Element> Eliminar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                DELETE FROM Entidad WHERE id = @id");
            db.AddInParameter(command, "id", DbType.Int32, entrada[0].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Insertar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                INSERT INTO Entidad(nombre, direccion, docId, telefonos, idTipo) VALUES(@nombre, @direccion, @docId, @telefonos, @idTipo)");
            db.AddInParameter(command, "nombre", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "direccion", DbType.String, entrada[2].Value);
            db.AddInParameter(command, "docId", DbType.String, entrada[3].Value);
            db.AddInParameter(command, "telefonos", DbType.String, entrada[4].Value);
            db.AddInParameter(command, "idTipo", DbType.Int32, entrada[5].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
        public static List<Element> Modificar(List<Element> entrada)
        {
            List<Element> rpta = new List<Element>();

            Database db = DatabaseFactory.CreateDatabase("dbItegracion");
            DbCommand command = db.GetSqlStringCommand(@"
                UPDATE Entidad SET nombre=@nombre, direccion=@direccion, docId=@docId, telefonos=@telefonos, idTipo=@idTipo WHERE id = @id");
            db.AddInParameter(command, "id", DbType.String, entrada[0].Value);
            db.AddInParameter(command, "nombre", DbType.String, entrada[1].Value);
            db.AddInParameter(command, "direccion", DbType.String, entrada[2].Value);
            db.AddInParameter(command, "docId", DbType.String, entrada[3].Value);
            db.AddInParameter(command, "telefonos", DbType.String, entrada[4].Value);
            db.AddInParameter(command, "idTipo", DbType.Int32, entrada[5].Value);
            db.ExecuteNonQuery(command);
            rpta.Add(new Element("rpta", "OK"));

            return rpta;
        }
    }
}
